﻿using Dapper;
using Microsoft.Data.SqlClient;
using Microsoft.EntityFrameworkCore;
using System.Data;
using TR.Domain.Entity;
using TR.Domain.Entity.BsItemType;
using TR.Domain.Repository.Bs_warehouseRepostiory;
using TR.Domain.Repository.sqlDto;

namespace TR.EFCore.Repository.Bs_wareRepostiory
{
    public class bs_wareRepostiory : Ibs_warehouseRepostiory
    {
        private readonly MyContext myContext;
        public bs_wareRepostiory(MyContext myContext)
        {
            this.myContext = myContext;
        }
        //查询ID
        public async Task<bs_WareHouse> warehouseSelectid(int id)
        {
            using (SqlConnection connection = new SqlConnection("Data Source=rm-cn-omn3o8dvy000aako.sqlserver.rds.aliyuncs.com,1433;Initial Catalog=TR;User ID=l_123;Password=Ff123456;Encrypt=False;"))
            {
                string sql = "Select * from [dbo].[bs_warehouse] where [id] = @id";
                // 使用参数化查询来避免 SQL 注入
                var result = await connection.QueryAsync<bs_WareHouse>(sql, new { id = id });
                // 返回单个结果，如果有多个结果，这里可以根据实际需要进行处理
                return result.FirstOrDefault();
            }
        }
        //逻辑删除
        public async Task<bool> warehouseUDelect(int id)
        {
            using (SqlConnection connection = new SqlConnection("Data Source=rm-cn-omn3o8dvy000aako.sqlserver.rds.aliyuncs.com, 1433;Initial Catalog=TR;User ID=l_123;Password=Ff123456;Encrypt=False;"))
            {
                string sql = $"delete from bs_warehouse where [id] = '{id}'";
                return connection.Execute(sql) > 0 ? true : false;
            }
        }
        //修改
        public Task<bool> warehouseUpdate(bs_WareHouse uid)
        {
            throw new NotImplementedException();
        }
        //添加仓库
        public async Task<bool> warehouseAdd(bs_WareHouse bs_Ware)
        {
            using (SqlConnection connection = new SqlConnection("Data Source=rm-cn-omn3o8dvy000aako.sqlserver.rds.aliyuncs.com,1433;Initial Catalog=TR;User ID=l_123;Password=Ff123456;Encrypt=False;"))
            {
                string sql = $"Insert into [dbo].[bs_warehouse](ware_code3,ware_name,ware_cap,sort_code,ware_duty_man,ware_adr,ware_state,is_lock,is_del)" +
                $"Values('{bs_Ware.ware_code3}','{bs_Ware.ware_name}','{bs_Ware.ware_cap}','{bs_Ware.sort_code}','{bs_Ware.ware_duty_man}','{bs_Ware.ware_adr}',0,'{bs_Ware.is_lock}','0')";
                return connection.Execute(sql) > 0 ? true : false;
            }
        }
        //查询
        public async Task<bs_WareHouse> warehouseSelete(int id)
        {
            using (SqlConnection connection = new SqlConnection("Data Source=rm-cn-omn3o8dvy000aako.sqlserver.rds.aliyuncs.com,1433;Initial Catalog=TR;User ID=l_123;Password=Ff123456;Encrypt=False;"))
            {
                string sql = "Select * from [dbo].[bs_WareHouse] where [id] = @id";
                // 使用参数化查询来避免 SQL 注入
                var result = await connection.QueryAsync<bs_WareHouse>(sql, new { id = id });
                // 返回单个结果，如果有多个结果，这里可以根据实际需要进行处理
                return result.FirstOrDefault();
            }
        }
        //分页查询

        public async Task<FenYe<bs_WareHouse>> warehouse_FenYe(int pageSize, int currentPage, string str)
        {
            using (SqlConnection con = new SqlConnection("Data Source=rm-cn-omn3o8dvy000aako.sqlserver.rds.aliyuncs.com,1433;Initial Catalog=TR;User ID=l_123;Password=Ff123456;Encrypt=False;"))
            {
                DynamicParameters dynamicParameters = new DynamicParameters();
                dynamicParameters.Add("@pageSize", pageSize);
                dynamicParameters.Add("@keyName", "id");
                dynamicParameters.Add("@tableName", "bs_warehouse");
                dynamicParameters.Add("@currentPage", currentPage);
                dynamicParameters.Add("@where", str);
                dynamicParameters.Add("@rows", direction: ParameterDirection.Output, dbType: DbType.Int32);
                // 添加额外参数用于 warearea 表的条件筛选
                dynamicParameters.Add("@wareCode", "your_warehouse_code");
                // 修改 SQL 查询语句，包括 warearea 表的查询和相同 ware_code 数量的计算
                string sql = @"
                DECLARE @Count int;
                SELECT @Count = COUNT(*)
                FROM bs_Ware_Area
                WHERE ware_code = @wareCode;
                EXEC [dbo].[procFenYe_Where] @pageSize, @keyName, @tableName, @currentPage, @where, @rows out;
                SELECT @Count AS WareAreaCount;";
                FenYe<bs_WareHouse> fenYe = new FenYe<bs_WareHouse>();
                var multiQuery = await con.QueryMultipleAsync(sql, dynamicParameters);
                // 获取 bs_warehouse 数据
                fenYe.List = await multiQuery.ReadAsync<bs_WareHouse>();
                // 获取 warearea 计数
                var wareAreaCount = await multiQuery.ReadFirstOrDefaultAsync<int>();
                fenYe.WareAreaCount = wareAreaCount;
                fenYe.Rows = dynamicParameters.Get<int>("rows");
                return fenYe;
            }
        }

        public async Task<bool> ItemUpdate(bs_WareHouse uid)
        {
            using (SqlConnection connection = new SqlConnection("Data Source=rm-cn-omn3o8dvy000aako.sqlserver.rds.aliyuncs.com,1433;Initial Catalog=TR;User ID=l_123;Password=Ff123456;Encrypt=False;"))
            {
                string sql = $"Update [dbo].[bs_warehouse] Set [ware_code] ='{uid.ware_code}' ,[ware_name]='{uid.ware_name}',[ware_state]='{uid.ware_state}',[ware_adr]='{uid.ware_adr}',[is_lock]='{uid.is_lock}',[sort_code]='{uid.sort_code}',[ware_duty_man]='{uid.ware_duty_man}',[ware_cap]='{uid.ware_cap}',[update_by]='{uid.update_by}',[is_del]='{uid.is_del}' where [id] ='{uid.Id}' ";
                return connection.Execute(sql) > 0 ? true : false;
            }
        }

        public async Task<bool> KWUpdate(List<int> id, string warecode)
        {
            try
            {
                var query = myContext;
                var xzkw = query.bs_Ware_Area.Where(p => id.Contains(p.id)).ToList();

                foreach (var item in xzkw)
                {
                    item.ware_code = warecode;
                    query.Entry(item).State = EntityState.Modified;
                }
                query.SaveChanges();

                return true;
            }
            catch (Exception)
            {
                Console.WriteLine("错误");
                return false;
            }
        }

        public async Task<bs_WareHouse> ItemSelectCode(string ware_code)
        {
            using (SqlConnection connection = new SqlConnection("Data Source=rm-cn-omn3o8dvy000aako.sqlserver.rds.aliyuncs.com,1433;Initial Catalog=TR;User ID=l_123;Password=Ff123456;Encrypt=False;"))
            {
                string sql = "Select * from [dbo].[bs_warehouse] where [ware_code] = @ware_code";
                // 使用参数化查询来避免 SQL 注入
                var result = await connection.QueryAsync<bs_WareHouse>(sql, new { ware_code = ware_code });
                // 返回单个结果，如果有多个结果，这里可以根据实际需要进行处理
                return result.FirstOrDefault();
            }
        }
    }
}
